package com.lx.ngsl.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;

/**
 * 
 * <p>Class       : com.neuqsoft.zx.common.utils.DaoUtil
 * <p>Descdription: DAO(Data Access Object) 数据访问对象工具方法类
 *</p>
 */
public class DaoUtil {

	/**
	 * 
	 * <p>Method ：queryList
	 * <p>Description : 原生分页查询方法
	 *
	 * @param em   EntityManager对象
	 * @param sql  带?占位符的sql语句
	 * @param pageable  分页对象
	 * @param args  sql占位符参数（顺序个数要与sql一致）
	 * @return Map<String,Object>
	 *</p>
	 */
	public final static Map<String,Object> queryPage(EntityManager em,String sql,Pageable pageable,String ... args){
    	int pagenumber=pageable.getPageNumber();
    	int pagesize=pageable.getPageSize();
    	Sort sort = pageable.getSort();
    	if(sort != null) {
    		//排序条件
    		String orderSql = " order by ";
    		Iterator<Order> it = sort.iterator();
    		while(it.hasNext()) {
    			Order a = it.next();
    			orderSql += a.getProperty() + " " + a.getDirection();
    			if(it.hasNext()) {
    				orderSql += ",";
    			}
    			
    		}
    		sql += orderSql;
    	}
    	//查询结果集的条数
    	String countsql="select count(1) shuliang  from ("+sql+")";
    	List<Map<String, Object>> countlist= query(em,countsql,args);
    	int totalElements=Integer.parseInt(countlist.get(0).get("SHULIANG").toString());
    	int totalPages=totalElements/pagesize+1;
    	
    	//查询分页数据
    	String datasql="select * from (select a.*,rownum rn from ("+sql+") a ) aa where 1=1 ";
    	int begin  = pagenumber*pagesize+1;
    	int end  =((pagenumber+1)*pagesize);
    	datasql = datasql + " and aa.rn >= "+begin+"";
    	datasql = datasql + " and aa.rn <= "+end+"";
    	List<Map<String, Object>> datalist = query(em,datasql,args);
    	//封装为含有分页信息的map对象
    	Map<String,Object> res=new HashMap<>();
    	res.put("content", datalist);
    	res.put("last",pagenumber+1==totalPages);
    	res.put("totalElements",totalElements);
    	res.put("totalPages",totalPages);
    	res.put("number",pagenumber);
    	res.put("size",pagesize);
    	res.put("numberOfElements",datalist.size());
    	res.put("sort", null);
    	res.put("first", pagenumber==0);
    	// 关闭EntityManager
    	em.close();
    	return res;
	}
	
	/**
	 * 
	 * <p>Method ：queryList
	 * <p>Description : 方法功能描述
	 *
	 * @param em  EntityManager对象
	 * @param sql 带 :xxx 占位符的sql语句
	 * @param params  占位符参数（个数需与sql中一致）
	 * @param pageable 分页对象
	 * @return Map<String,Object>
	 *</p>
	 */
	public final static Map<String,Object> queryPage(EntityManager em,String sql,Map<String,String> params,Pageable pageable){
		int pagenumber=pageable.getPageNumber();
		int pagesize=pageable.getPageSize();
		Sort sort = pageable.getSort();
		if(sort != null) {
			//排序条件
			String orderSql = " order by ";
			Iterator<Order> it = sort.iterator();
			while(it.hasNext()) {
				Order a = it.next();
				orderSql += a.getProperty() + " " + a.getDirection();
				if(it.hasNext()) {
					orderSql += ",";
				}
				
			}
			sql += orderSql;
		}
		//查询结果集的条数
		String countsql="select count(1) shuliang  from ("+sql+")";
		List<Map<String, Object>> countlist= query(em,countsql,params);
		int totalElements=Integer.parseInt(countlist.get(0).get("SHULIANG").toString());
		int totalPages=totalElements/pagesize+1;
		
		//查询分页数据
		String datasql="select * from (select a.*,rownum rn from ("+sql+") a ) aa where 1=1 ";
		int begin  = pagenumber*pagesize+1;
		int end  =((pagenumber+1)*pagesize);
		datasql = datasql + " and aa.rn >= "+begin+"";
		datasql = datasql + " and aa.rn <= "+end+"";
		List<Map<String, Object>> datalist = query(em,datasql,params);
		//封装为含有分页信息的map对象
		Map<String,Object> res=new HashMap<>();
		res.put("content", datalist);
		res.put("last",pagenumber+1==totalPages);
		res.put("totalElements",totalElements);
		res.put("totalPages",totalPages);
		res.put("number",pagenumber);
		res.put("size",pagesize);
		res.put("numberOfElements",datalist.size());
		res.put("sort", null);
		res.put("first", pagenumber==0);
		// 关闭EntityManager
		em.close();
		return res;
	}
	public final static Map<String,Object> queryPageLinked(EntityManager em,String sql,List<String> zds,Map<String,String> params,Pageable pageable){
		int pagenumber=pageable.getPageNumber();
		int pagesize=pageable.getPageSize();
		Sort sort = pageable.getSort();
		if(sort != null) {
			//排序条件
			String orderSql = " order by ";
			Iterator<Order> it = sort.iterator();
			while(it.hasNext()) {
				Order a = it.next();
				orderSql += a.getProperty() + " " + a.getDirection();
				if(it.hasNext()) {
					orderSql += ",";
				}
				
			}
			sql += orderSql;
		}
		//查询结果集的条数
		String countsql="select count(1) shuliang  from ("+sql+")";
		List<Map<String, Object>> countlist= query(em,countsql,params);
		int totalElements=Integer.parseInt(countlist.get(0).get("SHULIANG").toString());
		int totalPages=totalElements/pagesize+1;
		
		//查询分页数据
		String datasql="select * from (select a.*,rownum rn from ("+sql+") a ) aa where 1=1 ";
		int begin  = pagenumber*pagesize+1;
		int end  =((pagenumber+1)*pagesize);
		datasql = datasql + " and aa.rn >= "+begin+"";
		datasql = datasql + " and aa.rn <= "+end+"";
		List<LinkedHashMap<String, Object>> datalist = queryLinked(em,datasql,zds,params);
		//封装为含有分页信息的map对象
		Map<String,Object> res=new HashMap<>();
		res.put("content", datalist);
		res.put("last",pagenumber+1==totalPages);
		res.put("totalElements",totalElements);
		res.put("totalPages",totalPages);
		res.put("number",pagenumber);
		res.put("size",pagesize);
		res.put("numberOfElements",datalist.size());
		res.put("sort", null);
		res.put("first", pagenumber==0);
		// 关闭EntityManager
		em.close();
		return res;
	}
	
	/**
	 * 
	 * <p>Method ：query
	 * <p>Description : 原生查询sql执行方法
	 *
	 * @param em EntityManager对象
	 * @param sql 带？占位符的sql查询语句
	 * @param args 占位符参数（顺序个数要与sql一致）
	 * @return List<Map<String, Object>>
	 *</p>
	 */
	public final static List<Map<String, Object>> query(EntityManager em,String sql,String ... args){
		// 创建原生SQL查询QUERY实例
		Query query = em.createNativeQuery(sql);
		// 设置返回Map对象
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		int i = 1;
		for (String s : args) {
			query.setParameter(i, s);
			i++;
		}
		// 执行查询，返回的是实体结果集
		@SuppressWarnings("unchecked")
		List<Map<String, Object>> list = query.getResultList();
		// 关闭EntityManager
		em.close();
		return list;
	}


	/**
	 * 
	 * <p>Method ：query
	 * <p>Description : 通用的查询方法
	 *
	 * @param em EntityManager 对象
	 * @param sql 带：xxx 占位符的sql查询语句
	 * @param params 占位符参数
	 * @return List<Map<String, Object>>
	 * @author  yinzw
	 *</p>
	 */
	public final static List<Map<String, Object>> query(EntityManager em,String sql,Map<String,String> params){
		// 创建原生SQL查询QUERY实例
		Query query = em.createNativeQuery(sql);
		// 设置返回Map对象
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//占位符参数赋值
		for (Entry<String,String> entry : params.entrySet()) {
			query.setParameter(entry.getKey(), entry.getValue());
		}
		// 执行查询，返回的是实体结果集
		@SuppressWarnings("unchecked")
		List<Map<String, Object>> list = query.getResultList();
		
		// 关闭EntityManager
		em.close();
		return list;
	}
	/**
	 * 
	 * <p>Method ：queryLinked
	 * <p>Description : 查询字段有序
	 *
	 * @param em
	 * @param sql
	 * @param params
	 * @return 
	 *</p>
	 */
	public final static List queryLinked(EntityManager em,String sql,List<String> zds,Map<String,String> params){
		// 创建原生SQL查询QUERY实例
		Query query = em.createNativeQuery(sql);
		// 设置返回Map对象
		//query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		//占位符参数赋值
		for (Entry<String,String> entry : params.entrySet()) {
			query.setParameter(entry.getKey(), entry.getValue());
		}
		// 执行查询，返回的是实体结果集
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.getResultList();
		Map<String,Object> map = null;
		List<Map<String,Object>> linkedList = new ArrayList<>();
		Object[] objs = null;
		for(int i = 0;i < list.size();i++) {
			objs = list.get(i);
			map = new LinkedHashMap<String, Object>();
			for(int j = 0;j < zds.size();j++) {
				map.put(zds.get(j), objs[j]);
			}
			linkedList.add(map);
		}
		// 关闭EntityManager
		em.close();
		return linkedList;
	}
	/**
	 * 
	 * <p>Method ：queryAll
	 * <p>Description : 执行查询语句，所有查询结果封装为一个page格式
	 *
	 * @param em EntityManager对象
	 * @param sql 带:xxx占位符的sql
	 * @param params 占位符参数
	 * @return 
	 *</p>
	 */
	public static final Map<String,Object> queryAll(EntityManager em,String sql, Map<String, String> params) {
		String countsql = "select count(1) shuliang  from (" + sql + ")";
		List<Map<String, Object>> countlist = DaoUtil.query(em, countsql, params);
		int totalElements = Integer.parseInt(countlist.get(0).get("SHULIANG").toString());
		String datasql = "select * from (select a.*,rownum rn from (" + sql + ") a ) aa where 1=1 ";
		List<Map<String, Object>> datalist = DaoUtil.query(em,datasql, params);
		Map<String,Object> res = new HashMap<>();
		res.put("content", datalist);
		res.put("totalElements", totalElements);
		res.put("numberOfElements", datalist.size());
		res.put("sort", null);
		
		// 关闭EntityManager
		em.close();
		return res;

	}
}
